package com.kla.power.mapper;

import com.kla.power.entity.InverterElectricEntity;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kla.power.entity.StationEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 逆变器发电数据 Mapper 接口
 * </p>
 *
 * @author WuZH
 * @since 2024-06-02
 */
@Mapper
public interface InverterElectricMapper extends BaseMapper<InverterElectricEntity> {

    /**
     * 通过逆变器SN获取所有数据，并筛选结果集中时间最大的
     * @param inverterElectricEntity
     * @return
     */
    @Select ( value = "select * from (select * from inverter_electric_info where serialNumber=#{inverterElectricEntity.serialNumber}) isi order by isi.createTime desc limit 1" )
    InverterElectricEntity queryMaxDate ( @Param ( "inverterElectricEntity" ) InverterElectricEntity inverterElectricEntity );



    @Select ( value = "<script>SELECT * FROM inverter_electric_info iei " +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterElectricEntity.id!=null'> AND iei.id=#{inverterElectricEntity.id} </if>" +
            "<if test='inverterElectricEntity.collectorSn!=null'> AND iei.collectorSn=#{inverterElectricEntity.collectorSn} </if>" +
            "<if test='inverterElectricEntity.serialNumber!=null'> AND iei.serialNumber=#{inverterElectricEntity.serialNumber} </if>" +
            "ORDER BY iei.createTime DESC LIMIT 1</trim></script>")
    InverterElectricEntity querySingle( @Param  ( "inverterElectricEntity" )InverterElectricEntity inverterElectricEntity);


    /**
     * 查询某商户下每个电站今天发电总数，根据电站分组显示
     * @param stationEntity
     * @return
     */
    @Select ( value = "SELECT si.id, isi.totaEnergy, isi.todayEnergy, si.stationName, isi.gridPower, di.deviceSn, di.power, si.address, isi.createTime \n" +
            "FROM inverter_electric_info isi\n" +
            "RIGHT JOIN (SELECT serialNumber, MAX(createTime) AS createTime FROM inverter_electric_info WHERE date_format(createTime, '%Y-%m-%d') = date_format(NOW(), '%Y-%m-%d') GROUP BY serialNumber) tmp\n" +
            "ON isi.createTime = tmp.createTime AND isi.serialNumber = tmp.serialNumber\n" +
            "         LEFT JOIN device_info di ON isi.serialNumber = di.deviceSn\n" +
            "         LEFT JOIN station_info si ON di.stationId = si.id\n" +
            "WHERE " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +

            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids:=#{stationEntity.merchantId}) t2 ) t3 where ischild != 0\n" +
            "                                    UNION\n" +
            "                                    SELECT id\n" +
            "                                    FROM merchant_info pi\n" +
            "                                    WHERE pi.id=#{stationEntity.merchantId})")
    List<StationEntity> queryPowerGroupStation(@Param ("stationEntity") StationEntity stationEntity);


    /**
     * 通过逆变器分组，查询一个电站下所有逆变器此时此刻的最大发电量数据
     * @return
     */
    @Select ( value = "SELECT isi.id,\n" +
            "       isi.todayEnergy,\n" +
            "       isi.totaEnergy,\n" +
            "       si.stationName,\n" +
            "       di.deviceSn,\n" +
            "       di.deviceTitle AS inverterTitle,\n" +
            "       isi.gridPower,\n" +
            "       si.capacity,\n" +
            "       si.address,\n" +
            "       isi.createTime\n" +
            "FROM  device_info di LEFT JOIN station_info si ON di.stationId = si.id\n" +
            "         RIGHT JOIN (SELECT cisi.* FROM inverter_electric_info cisi JOIN (\n" +
            "                        SELECT serialNumber, MAX(createTime) AS createTime\n" +
            "                        FROM inverter_electric_info \n" +
            "                        WHERE DATE_FORMAT(createTime, '%Y-%m-%d') = DATE_FORMAT(current_date(), '%Y-%m-%d')\n" +
            "                        GROUP BY serialNumber\n" +
            "                    ) isi_temp ON cisi.serialNumber = isi_temp.serialNumber AND cisi.createTime = isi_temp.createTime) isi\n" +
            "                   ON isi.serialNumber = di.deviceSn\n" +
            "  WHERE si.id=#{stationEntity.id}\n" +
            "    AND di.deviceType=2\n" +
            "GROUP BY isi.serialNumber")
    List<InverterElectricEntity> queryPowerGroupInverter (@Param ("stationEntity") StationEntity stationEntity);



    /**
     * 查询一个电站下所有逆变器的发电数据
     * @param stationEntity
     * @return
     */
    @Select (value = "SELECT isi.id,isi.todayEnergy,isi.totaEnergy,isi.gridPower,isi.serialNumber,isi.collectorSn,isi.createTime\n" +
            "FROM inverter_electric_info isi\n" +
            "LEFT JOIN device_info di on isi.serialNumber = di.deviceSn\n" +
            "WHERE di.stationId=#{stationEntity.id}\n" +
            "  AND date_format(isi.createTime, '%Y-%m-%d') >= date_format(#{stationEntity.startTime}, '%Y-%m-%d')\n" +
            "  AND date_format(isi.createTime, '%Y-%m-%d') <= date_format(#{stationEntity.endTime}, '%Y-%m-%d')")
    List<InverterElectricEntity> queryListByStation (@Param ("stationEntity") StationEntity stationEntity);


    /**
     * 查询一台逆变器今天发电最大值
     * @param inverterElectricEntity
     * @return
     */
    @Select(value = "SELECT * FROM inverter_electric_info isi\n" +
            "WHERE isi.serialNumber = #{inverterElectricEntity.serialNumber}\n" +
            "  AND isi.createTime in (\n" +
            "    SELECT max(createTime)\n" +
            "    FROM inverter_electric_info\n" +
            "    WHERE date_format(createTime, '%Y-%m-%d') = current_date()\n" +
            "      AND serialNumber = #{inverterElectricEntity.serialNumber})")
    InverterElectricEntity queryInverterTodayMax(@Param("inverterElectricEntity") InverterElectricEntity inverterElectricEntity);

    /**
     * 查询逆变器的历史数据，查询从startTime到endTime时间内的发电数据
     * @param inverterElectricEntity
     * @return
     */
    @Select ( value = "<script>SELECT * FROM inverter_electric_info isi " +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterElectricEntity.serialNumber!=null'> AND isi.serialNumber=#{inverterElectricEntity.serialNumber} </if>" +
            " AND date_format(isi.createTime, '%Y-%m-%d') &gt;= date_format(#{inverterElectricEntity.startTime}, '%Y-%m-%d') AND date_format(isi.createTime, '%Y-%m-%d') &lt;= date_format(#{inverterElectricEntity.endTime}, '%Y-%m-%d') " +
            "</trim></script>" )
    List<InverterElectricEntity> queryHistoryData ( @Param ( "inverterElectricEntity" ) InverterElectricEntity inverterElectricEntity );



    /**
     * 大屏满发小时排名，查询一个商户下所有电站的当日总发电量/电站装机容量，进行排名
     * @param stationEntity
     * @return
     */
    //@Select ( value = "SELECT isi.id,\n" +
    //        "       SUM(isi.todayEnergy) AS todayEnergy,\n" +
    //        "       si.stationName,\n" +
    //        "       COALESCE(SUM( isi.gridPower ), 0 ) AS gridPower,\n" +
    //        "       di.deviceSn,\n" +
    //        "       COALESCE(SUM( di.power ), 0 ) AS power,\n" +
    //        "       si.address,\n" +
    //        "       isi.createTime\n" +
    //        "from station_info si\n" +
    //        "         LEFT JOIN device_info di ON si.id = di.stationId\n" +
    //        "         LEFT JOIN\n" +
    //        "             (SELECT max(createTime) AS createTime, serialNumber\n" +
    //        "              FROM inverter_electric_info\n" +
    //        "              WHERE date_format(createTime, '%Y-%m-%d') = date_format(#{stationEntity.createTime}, '%Y-%m-%d')\n" +
    //        "              GROUP BY serialNumber) temp ON temp.serialNumber = di.deviceSn\n" +
    //        "         LEFT JOIN inverter_electric_info isi ON temp.serialNumber = isi.serialNumber AND temp.createTime = isi.createTime\n" +
    //        "WHERE " +
    //        "si.merchantId IN (SELECT id\n" +
    //        "                        FROM (SELECT t1.id,\n" +
    //        "                                     t1.parentId,\n" +
    //        "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
    //        "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
    //        "                                   (SELECT @pids:=#{stationEntity.merchantId}) t2 ) t3 where ischild != 0\n" +
    //        "                                    UNION\n" +
    //        "                                    SELECT id\n" +
    //        "                                    FROM merchant_info pi\n" +
    //        "                                    WHERE pi.id=#{stationEntity.merchantId}) " +
    //        " AND si.type=1\n" +
    //        "GROUP BY si.id")
    //List<StationEntity> dashboardFullHour(@Param ("stationEntity") StationEntity stationEntity);

    /**
     * 一次性求电站的所有逆变器当日发电数量
     * @return
     */
    //@Select (value = "<script>SELECT cisi.*\n" +
    //        "FROM inverter_electric_info cisi\n" +
    //        "         JOIN (SELECT serialNumber, MAX(createTime) AS createTime\n" +
    //        "               FROM inverter_electric_info\n" +
    //        "               WHERE DATE_FORMAT(createTime, '%Y-%m-%d') = DATE_FORMAT(current_date(), '%Y-%m-%d')\n" +
    //        "               GROUP BY serialNumber) isi_temp ON cisi.serialNumber = isi_temp.serialNumber AND\n" +
    //        "                                                  cisi.createTime = isi_temp.createTime</script>")
    //List<InverterElectricEntity> queryInverterElectricEntities ();

}
